package com.loie.datacenter.web.controller;

import com.alibaba.fastjson.JSONObject;
import com.loie.datacenter.comm.CommJSON;
import com.loie.datacenter.comm.ExcelConst;
import com.loie.datacenter.model.bo.*;
import com.loie.datacenter.service.CommissionService;
import com.loie.datacenter.service.UserListService;
import com.loie.datacenter.util.ExcelUtil;
import com.loie.datacenter.util.SecureUtil;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zhl
 * @create 2021/9/11 19:55
 */
@Slf4j
@RequestMapping("/commission")
@RestController
@AllArgsConstructor
public class CommissionController {

    private final CommissionService commissionService;
    private final RedisTemplate<String, String> redisTemplate;
    private final UserListService userListService;

    @PostMapping("/getCommissionDetail")
    public
    CommJSON getCommissionDetail(@RequestBody String msg, @CurrentUser TokenInfoBO tokenInfoBO) {
        CommJSON commJson = new CommJSON();
        try {
            String aesSecureKey = redisTemplate.opsForValue().get(SecureUtil.MD5Encode(String.format("[DataCenter][secretKey][%s]",
                    tokenInfoBO.getToken())));
            String mwMsg = SecureUtil.decryptAES(msg, aesSecureKey);
            CommissionItemBO bo = JSONObject.parseObject(mwMsg, CommissionItemBO.class);

            List<CommissionItemBO> datalist = commissionService.getCommissionItemList(bo);
            CommissionItemSumBO commissionItemSum = commissionService.getCommissionItemSum(bo);
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("list", datalist);
            String cartonQtySum = commissionItemSum.getCartonQtySum();
            String volumeSum = commissionItemSum.getVolumeSum();
            String shippingQtySum = commissionItemSum.getShippingQtySum();
            String salesAmountSum = commissionItemSum.getSalesAmountSum();
            jsonObject.put("cartonQtySum", cartonQtySum == null ? 0 : cartonQtySum);
            jsonObject.put("volumeSum", volumeSum == null ? 0 : volumeSum);
            jsonObject.put("shippingQtySum", shippingQtySum == null ? 0 : shippingQtySum);
            jsonObject.put("salesAmountSum", salesAmountSum == null ? 0 : salesAmountSum);
            commJson.setSuc();
            commJson.setData(SecureUtil.encryptAES(jsonObject.toJSONString(), aesSecureKey));
        } catch (Exception e) {
            commJson.setErrorMessage("Sorry, Query exception(code:1302)");
            commJson.setError();
            e.printStackTrace();
            log.error("CommissionController.getCommissionDetail()===>", e);
        }
        return commJson;
    }

    @PostMapping("/getCommissionList")
    public
    CommJSON getCommissionList(@RequestBody String msg, @CurrentUser TokenInfoBO tokenInfoBO) {
        CommJSON commJson = new CommJSON();
        try {
            String aesSecureKey = redisTemplate.opsForValue().get(SecureUtil.MD5Encode(String.format("[DataCenter][secretKey][%s]",
                    tokenInfoBO.getToken())));
            String mwMsg = SecureUtil.decryptAES(msg, aesSecureKey);
            CommissionBO bo = JSONObject.parseObject(mwMsg, CommissionBO.class);

            List<CommissionBO> dataList = commissionService.getCommissionList(bo, tokenInfoBO.getUser());

            String accountReceivableSum = null;
            String receivedAccountSum = null;
            String remainAccountSum = null;
            CommissionItemSumBO commissionItemSumBO = null;
            int total = 0;
            if (StringUtils.isBlank(bo.getCommissionPaid())) {
                commissionItemSumBO = commissionService.getCommissionCount(bo, tokenInfoBO.getUser());
                accountReceivableSum = commissionItemSumBO.getAccountReceivableSum();
                total = commissionItemSumBO.getTotal();
                bo.setCommissionPaid("1");
                commissionItemSumBO = commissionService.getCommissionCount(bo, tokenInfoBO.getUser());
                receivedAccountSum = commissionItemSumBO.getReceivedAccountSum();
                bo.setCommissionPaid("0");
                commissionItemSumBO = commissionService.getCommissionCount(bo, tokenInfoBO.getUser());
                remainAccountSum = commissionItemSumBO.getRemainAccountSum();
            } else {
                commissionItemSumBO = commissionService.getCommissionCount(bo, tokenInfoBO.getUser());
                if ("1".equals(bo.getCommissionPaid())) {
                    receivedAccountSum = commissionItemSumBO.getReceivedAccountSum();
                } else {
                    remainAccountSum = commissionItemSumBO.getRemainAccountSum();
                }
                accountReceivableSum = commissionItemSumBO.getAccountReceivableSum();
                total = commissionItemSumBO.getTotal();
            }


            JSONObject jsonObject = new JSONObject();
            jsonObject.put("total", total);
            jsonObject.put("payableSum", accountReceivableSum == null ? 0 : accountReceivableSum);
            jsonObject.put("paidSum", receivedAccountSum == null ? 0 : receivedAccountSum);
            jsonObject.put("unpaidSum", remainAccountSum == null ? 0 : remainAccountSum);
            jsonObject.put("list", dataList);

            commJson.setSuccess(true);
            commJson.setData(SecureUtil.encryptAES(jsonObject.toJSONString(), aesSecureKey));
        } catch (Exception e) {
            commJson.setErrorMessage("Sorry, Query exception(code:1301)");
            commJson.setError();
            e.printStackTrace();
            log.error("CommissionController.getCommissionList()===>", e);
        }
        return commJson;
    }

    /**
     * 导出参加信息表
     *
     * @param res
     * @throws Exception
     */
    @PostMapping(value = "/downloadCommissionList")
    public void downloadTaskInfo(@RequestBody String msg, @CurrentUser TokenInfoBO tokenInfoBO, HttpServletResponse res) throws Exception {
        try {
            String aesSecureKey = redisTemplate.opsForValue().get(SecureUtil.MD5Encode(String.format("[DataCenter][secretKey][%s]",
                    tokenInfoBO.getToken())));

            UserListBO userListBO = userListService.getUserInfoByUser(tokenInfoBO.getUser());
            String mwMsg = SecureUtil.decryptAES(msg, aesSecureKey);
            CommissionBO bo = JSONObject.parseObject(mwMsg, CommissionBO.class);
            bo.setPageIndex(-1);
            List<CommissionBO> dataList = commissionService.getCommissionList(bo, tokenInfoBO.getUser());
            List<JSONObject> headList = new ArrayList<>();
            int authority = userListBO.getDataAuthority();

            List<List<String>> lists = new ArrayList<>();

            JSONObject jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Invoice NO.");
            jsonObject.put(ExcelConst.WIDTH, 3500);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "PO NO.");
            jsonObject.put(ExcelConst.WIDTH, 3500);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);


            if (authority == 2) {
                jsonObject = new JSONObject();
                jsonObject.put(ExcelConst.TITLE, "Customer Name");
                jsonObject.put(ExcelConst.WIDTH, 5000);
                jsonObject.put(ExcelConst.TYPE, "1");
                headList.add(jsonObject);
            }
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "ETD");
            jsonObject.put(ExcelConst.WIDTH, 3000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);

            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Trade Term");
            jsonObject.put(ExcelConst.WIDTH, 3000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "POL");
            jsonObject.put(ExcelConst.WIDTH, 3000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "POD");
            jsonObject.put(ExcelConst.WIDTH, 3000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Payment Term");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Due Date");
            jsonObject.put(ExcelConst.WIDTH, 3000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Currency");
            jsonObject.put(ExcelConst.WIDTH, 2000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Amount");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Commission Amount");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Commission Status");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Commission Paid");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Commission Unpaid");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Commission Comments");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Payable");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Paid");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Unpaid");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            headList.add(jsonObject);

            for (int i = 0; i < dataList.size(); i++) {
                List<String> list = new ArrayList<>();
                list.add(dataList.get(i).getInvoiceNO());
                list.add(dataList.get(i).getCustomerContractNo());
                if (authority == 2) {
                    list.add(dataList.get(i).getCustomerCorpName());
                }
                list.add(dataList.get(i).getShippingDate());
                list.add(dataList.get(i).getTermOfPrice());
                list.add(dataList.get(i).getLoadingPort());
                list.add(dataList.get(i).getDestinationPort());
                list.add(dataList.get(i).getPayment());
                list.add(dataList.get(i).getCollectionDate());
                list.add(dataList.get(i).getCurrency());
                list.add(dataList.get(i).getTotalSalesAmount());

                list.add(dataList.get(i).getPayCommission());
                String status = dataList.get(i).getCommissionPaid();
                list.add("0".equals(status) ? "Unpaid" : "1".equals(status) ? "Paid" : status);
                list.add(dataList.get(i).getCommissionPaidAmount());
                list.add(dataList.get(i).getCommissionUnPaidAmount());
                list.add(dataList.get(i).getCommissionComments());
                list.add(dataList.get(i).getAccountReceivable());
                list.add(dataList.get(i).getReceivedAccount());
                list.add(dataList.get(i).getRemainAccount());

                lists.add(list);
            }
            ExcelUtil.getExcel(res, lists, headList);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("CommissionController.downloadTaskInfo()===>", e);
        }
    }

    /**
     * 导出参加信息表
     *
     * @param res
     * @throws Exception
     */
    @PostMapping(value = "/downloadCommissionItemList")
    public void downloadCommissionItemList(@RequestBody String msg, @CurrentUser TokenInfoBO tokenInfoBO, HttpServletResponse res) throws Exception {
        try {
            String aesSecureKey = redisTemplate.opsForValue().get(SecureUtil.MD5Encode(String.format("[DataCenter][secretKey][%s]",
                    tokenInfoBO.getToken())));

            UserListBO userListBO = userListService.getUserInfoByUser(tokenInfoBO.getUser());
            String mwMsg = SecureUtil.decryptAES(msg, aesSecureKey);
            CommissionBO bo = JSONObject.parseObject(mwMsg, CommissionBO.class);
            bo.setPageIndex(-1);
            List<CommissionWithItemBO> dataList = commissionService.getCommissionWithItemList(bo, tokenInfoBO.getUser());
            List<JSONObject> headList = new ArrayList<>();
            int authority = userListBO.getDataAuthority();

            List<List<String>> lists = new ArrayList<>();
            List<List<String>> cellRangeLists = new ArrayList<>();

            JSONObject jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Invoice NO.");
            jsonObject.put(ExcelConst.WIDTH, 3500);
            jsonObject.put(ExcelConst.TYPE, "1");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "PO NO.");
            jsonObject.put(ExcelConst.WIDTH, 3500);
            jsonObject.put(ExcelConst.TYPE, "1");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);


            if (authority == 2) {
                jsonObject = new JSONObject();
                jsonObject.put(ExcelConst.TITLE, "Customer Name");
                jsonObject.put(ExcelConst.WIDTH, 5000);
                jsonObject.put(ExcelConst.TYPE, "1");
                jsonObject.put(ExcelConst.MERGE, "1");
                headList.add(jsonObject);
            }
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "ETD");
            jsonObject.put(ExcelConst.WIDTH, 3000);
            jsonObject.put(ExcelConst.TYPE, "1");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);

            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Trade Term");
            jsonObject.put(ExcelConst.WIDTH, 3000);
            jsonObject.put(ExcelConst.TYPE, "1");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "POL");
            jsonObject.put(ExcelConst.WIDTH, 3000);
            jsonObject.put(ExcelConst.TYPE, "1");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "POD");
            jsonObject.put(ExcelConst.WIDTH, 3000);
            jsonObject.put(ExcelConst.TYPE, "1");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Payment Term");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "1");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Due Date");
            jsonObject.put(ExcelConst.WIDTH, 3000);
            jsonObject.put(ExcelConst.TYPE, "1");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Currency");
            jsonObject.put(ExcelConst.WIDTH, 2000);
            jsonObject.put(ExcelConst.TYPE, "1");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Amount");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Commission Amount");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Commission Status");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "1");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Commission Paid");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Commission Unpaid");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Commission Comments");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "1");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Payable");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Paid");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Unpaid");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            jsonObject.put(ExcelConst.MERGE, "1");
            headList.add(jsonObject);

            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "ItemNo.");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "CustomerItemNo.");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Product Name");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Shipped Qty");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Unit");
            jsonObject.put(ExcelConst.WIDTH, 2000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Sales Price");
            jsonObject.put(ExcelConst.WIDTH, 3000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.0000");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Sales Amount");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.00");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Packing");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "1");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Pieces Per Carton");
            jsonObject.put(ExcelConst.WIDTH, 3000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "Carton Qty");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0");
            headList.add(jsonObject);
            jsonObject = new JSONObject();
            jsonObject.put(ExcelConst.TITLE, "CBM");
            jsonObject.put(ExcelConst.WIDTH, 4000);
            jsonObject.put(ExcelConst.TYPE, "2");
            jsonObject.put(ExcelConst.FORMAT, "#,#0.000");
            headList.add(jsonObject);


            for (int i = 0; i < dataList.size(); i++) {
                List<String> list = new ArrayList<>();
                list.add(dataList.get(i).getInvoiceNO());
                list.add(dataList.get(i).getCustomerContractNo());
                if (authority == 2) {
                    list.add(dataList.get(i).getCustomerCorpName());
                }
                list.add(dataList.get(i).getShippingDate());
                list.add(dataList.get(i).getTermOfPrice());
                list.add(dataList.get(i).getLoadingPort());
                list.add(dataList.get(i).getDestinationPort());
                list.add(dataList.get(i).getPayment());
                list.add(dataList.get(i).getCollectionDate());
                list.add(dataList.get(i).getCurrency());
                list.add(dataList.get(i).getTotalSalesAmount());

                list.add(dataList.get(i).getPayCommission());
                String status = dataList.get(i).getCommissionPaid();
                list.add("0".equals(status) ? "Unpaid" : "1".equals(status) ? "Paid" : status);
                list.add(dataList.get(i).getCommissionPaidAmount());
                list.add(dataList.get(i).getCommissionUnPaidAmount());
                list.add(dataList.get(i).getCommissionComments());
                list.add(dataList.get(i).getAccountReceivable());
                list.add(dataList.get(i).getReceivedAccount());
                list.add(dataList.get(i).getRemainAccount());

                list.add(dataList.get(i).getItemNo());
                list.add(dataList.get(i).getCustomerItemNo());
                list.add(dataList.get(i).getEngItemName());
                list.add(dataList.get(i).getShippingQty());
                list.add(dataList.get(i).getUnit());
                list.add(dataList.get(i).getSalesPrice());
                list.add(dataList.get(i).getSalesAmount());
                list.add(dataList.get(i).getEngPacking());
                list.add(dataList.get(i).getOuterCapacity());
                list.add(dataList.get(i).getCartonQty());
                list.add(dataList.get(i).getVolume());
                lists.add(list);

                List<String> cellList = new ArrayList<>();
                cellList.add(dataList.get(i).getInvoiceNO());
                cellRangeLists.add(cellList);
            }
            ExcelUtil.getExcelWithCellRange(res, lists, headList, cellRangeLists);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("CommissionController.downloadCommissionItemList()===>", e);
        }
    }
}
